package com.woniuxy.dao;

import com.woniuxy.dto.ResultChangeServer;
import com.woniuxy.dto.ResultServerDetail;
import com.woniuxy.pojo.Servers;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ServersMapper {
    int deleteByPrimaryKey(Long id);

    int insert(Servers record);

    int insertSelective(Servers record);

    Servers selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(Servers record);

    int updateByPrimaryKey(Servers record);

    //根据服务分组，在统计使用次数之和，取前三
    @Select("SELECT server_menu_id FROM servers GROUP BY server_menu_id  ORDER BY SUM(server_num) DESC LIMIT 0,3")
    List<Long> findHotServer();

    @Select("SELECT * FROM servers WHERE server_menu_id  = #{id}")
    List<Servers> findByServerMenuId(Long serverMenuId);

    @Select("SELECT sm.id,sm.server_img,sm.server_name,s.supplier_id,s.server_num,s.server_type,s.price,v.vendor_name FROM servers s\n" +
            " LEFT JOIN vendor v " +
            " ON s.supplier_id = v.id " +
            " LEFT JOIN server_menu sm " +
            " ON sm.id = s.server_menu_id " +
            " WHERE s.server_menu_id = #{id}")
    List<ResultChangeServer> findServersByMenuId(Long serverMenuId);

    @Select("SELECT s.id,sm.id smid,v.vendor_name,v.head_img,sm.server_name,v.user_name,s.price FROM vendor v  " +
            "LEFT JOIN servers s " +
            "ON v.id = s.supplier_id " +
            "LEFT JOIN server_menu sm " +
            "ON s.server_menu_id = sm.id " +
            "WHERE v.id = #{mid} AND sm.id = #{id} AND s.server_type =#{typeId}")
    ResultServerDetail findProviderByMidAndTypeId(@Param("mid") Long mid, @Param("typeId") Integer typeId, @Param("id") Long id);

    @Select("SELECT s.id,sm.id smid,p.company AS vendor_name,p.head_img,sm.server_name,s.price FROM property p  " +
            "LEFT JOIN servers s " +
            "ON p.id = s.supplier_id " +
            "LEFT JOIN server_menu sm " +
            "ON s.server_menu_id = sm.id " +
            "WHERE p.id = #{mid} AND sm.id =#{id}  AND s.server_type =#{typeId}")
    ResultServerDetail findProviderByPidAndTypeId(@Param("mid") Long mid, @Param("typeId") Integer typeId, @Param("id") Long id);
}